﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static ContatoVO getContato(int idContato, string contato)
        {
            ContatoVO contatoVO = null;
            List<ContatoVO> listContatoVOs = getContatos(idContato, contato,null);
            if (listContatoVOs != null && listContatoVOs.Count > 0)
            {
                contatoVO = listContatoVOs[0];
            }
            return contatoVO;
        }

        public static List<ContatoVO> getContatos()
        {
            return getContatos(null,null,null);
        }

        public static List<ContatoVO> getContatos(int? idContato, string nome, string local)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT IdContato, Nome, Local, Obs, Cadastro, CadastradoPor, Edicao, EditadoPor ");
            query.AppendLine("FROM contato ");

            if (idContato != null)
            {
                query.AppendLine(where + "IdContato=?IdContato ");
                where = "AND ";
            }
            if (nome != null)
            {
                query.AppendLine(where + "Nome LIKE ?Nome ");
                where = "AND ";
            }
            if (local != null)
            {
                query.AppendLine(where + "Local LIKE ?Local ");
                where = "AND ";
            }

            query.AppendLine("ORDER BY Nome ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idContato != null) cmd.Parameters.AddWithValue("?IdContato", idContato);
            if (nome != null) cmd.Parameters.AddWithValue("?Nome", "%" + nome + "%");
            if (local != null) cmd.Parameters.AddWithValue("?Local", "%" + local + "%");

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<ContatoVO> listContato = new List<ContatoVO>();

                while (reader.Read())
                {
                    ContatoVO contatoVO = ContatoVO.CreateContatoVO();
                    contatoVO.idContato = reader.GetInt32(reader.GetOrdinal("IdContato"));
                    contatoVO.nome = reader.GetString(reader.GetOrdinal("Nome"));
                    contatoVO.local = reader.GetString(reader.GetOrdinal("Local"));
                    contatoVO.obs = reader.GetString(reader.GetOrdinal("Obs"));
                    contatoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("Cadastro"));
                    contatoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("CadastradoPor"));
                    contatoVO.edicao = reader.GetDateTime(reader.GetOrdinal("Edicao"));
                    contatoVO.editadoPor = reader.GetString(reader.GetOrdinal("EditadoPor"));
                    listContato.Add(contatoVO);
                }
                reader.Close();
                return listContato;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setContato(ContatoVO contatoVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE contato ");
            query.AppendLine("SET Nome=?Nome, Local=?Local, Obs=?Obs, Cadastro=?Cadastro, CadastradoPor=?CadastradoPor, Edicao=?Edicao, EditadoPor=?EditadoPor ");
            query.AppendLine("WHERE IdContato=?IdContato ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdContato", contatoVO.idContato);
            cmd.Parameters.AddWithValue("?Nome", contatoVO.nome);
            cmd.Parameters.AddWithValue("?Local", contatoVO.local);
            cmd.Parameters.AddWithValue("?Obs", contatoVO.obs);
            cmd.Parameters.AddWithValue("?Cadastro", contatoVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", contatoVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", contatoVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", contatoVO.editadoPor);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newContato(ContatoVO contatoVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO contato ");
            query.AppendLine("  ( Nome, Local, Obs, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
            query.AppendLine("VALUES (?Nome, ?Local, ?Obs, ?Cadastro, ?CadastradoPor, ?Edicao, ?EditadoPor ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Nome", contatoVO.nome);
            cmd.Parameters.AddWithValue("?Local", contatoVO.local);
            cmd.Parameters.AddWithValue("?Obs", contatoVO.obs);
            cmd.Parameters.AddWithValue("?Cadastro", contatoVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", contatoVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", contatoVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", contatoVO.editadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delContato(ContatoVO contatoVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM contato ");
            query.AppendLine("WHERE IdContato=?IdContato ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdContato", contatoVO.idContato);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
